﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;


namespace TooltracePC
{
    class dbUser : clsDatabase
    {
        private static int nUserCount = 0;


        public int getUserCount()
        {
            if (nUserCount > 0)
            {
                return nUserCount;
            }
            setUserCount();
            return nUserCount;
        }

        /*set*/
        private void setUserCount()
        {
            string strSql = @"select count(*) AS c from Staff";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            if (dtTool != null)
            {
                nUserCount = (int)dtTool.Rows[0]["c"];
            }else
            {
                nUserCount = 0;
            }
            
        }

        //get all
        public DataTable getAll()
        {
            string strSql = @"select UserID, UserNo, UserName from Staff";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);

            return dtTool;
        }

        public DataTable getAll2()
        {
            string strSql = @"select LableID, UserNo, UserName, UserPass, UserPower from Staff";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);

            return dtTool;
        }


        //get by no
        public DataTable getAccName(string strName)
        {
            string strSql = @"select UserID, UserNo, UserName from Staff where UserName = '" + strName + "'";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            return dtTool;
        }

        public DataTable getAccNo(string strName)
        {
            string strSql = @"select UserID, UserNo, UserName from Staff where UserNo = '" + strName + "'";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            return dtTool;
        }



        //get by name
        public DataTable getByName(string strName)
        {
            string strSql = @"select UserID, UserNo, UserName from Staff where UserName Like '%" + strName + "%' or UserNo Like '%" + strName + "%'";
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            return dtTool;
        }

        public DataTable getByID(int nID)
        {

            string strSql = @"select * from Staff where UserID = " + nID;
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            return dtTool;
        }



        //update
        public int updateDb(int _id, string LableID, string UserName, string UserNo, string UserPass)
        {
            string strSQL;

            if (string.IsNullOrEmpty(UserName) || string.IsNullOrEmpty(UserNo) || string.IsNullOrEmpty(UserPass))
            {
                MessageBox.Show("必填项不能为空", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            DataTable _dt = null;
            _dt = getAccName(UserName);
            if (_dt.Rows.Count > 0)
            {
                if(int.Parse(_dt.Rows[0]["UserID"].ToString()) != _id)
                {
                    MessageBox.Show("此用户已存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return 0;
                }
            }

            _dt = getAccNo(UserNo);
            if (_dt.Rows.Count > 0)
            {
                if (int.Parse(_dt.Rows[0]["UserID"].ToString()) != _id)
                {
                    MessageBox.Show("此用户已存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return 0;
                }
            }

            strSQL = "update Staff set LableID = '" + LableID + "', UserName = '" + UserName + "', UserNo = '" + UserNo + "', UserPass = '"+ UserPass + "' where UserID = " + _id;
            execNonSQL(strSQL);
            return 1;
        }


        //inster LableID, UserName, UserNo, UserPass
        public int insertDb(string LableID, string UserName, string UserNo, string UserPass)
        {
            string strSQL;

            if (string.IsNullOrEmpty(UserName) || string.IsNullOrEmpty(UserNo) || string.IsNullOrEmpty(UserPass))
            {
                MessageBox.Show("必填项不能为空", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            DataTable _dt = null;
            _dt = getAccName(UserName);
            if(_dt.Rows.Count > 0)
            {
                MessageBox.Show("此用户已存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            _dt = getAccNo(UserNo);
            if (_dt.Rows.Count > 0)
            {
                MessageBox.Show("此用户已存在", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            strSQL = "insert into Staff(LableID, UserNo, UserName, UserPass) values('" + LableID + "', '" + UserNo + "', '" + UserName + "','"+ UserPass + "')";
            execNonSQL(strSQL);
            return 1;
        }


        //delete
        public int deleteDb(int[] _id)
        {
            int nResutl = 0;
            string strSQL = "delete from Staff where UserID in (";
            if (_id == null || _id.Length == 0)
            {
                return nResutl;
            }

            for (int i = 0; i < _id.Length; i++)
            {
                if (i + 1 == _id.Length)
                {
                    strSQL += _id[i].ToString();
                }
                else
                {
                    strSQL += _id[i].ToString() + ",";
                }

            }

            strSQL += ")";

            execNonSQL(strSQL);

            nResutl = 1;
            return nResutl;
        }




    }
}
